<?php

/**
 * SQL对象化转化为SQL语句
 */
/**
 * CDbCommandBuilder class file.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @link http://www.yiiframework.com/
 * @copyright 2008-2013 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
/**
 * CDbCommandBuilder provides basic methods to create query commands for tables.
 *
 * @property CDbConnection $dbConnection Database connection.
 * @property CDbSchema $schema The schema for this command builder.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @package system.db.schema
 * @since 1.0
 */

namespace library\Core\Db;

use library\Core\Db\CDbCriteriaBuilder;
use library\Core\Db\CDbSqlPrepare;
use library\Core\Exception\DbException;

class CDbSqlBuilder {

    const PARAM_PREFIX = ':bop';

    /**
     * @param CDbSchema $schema the schema for this command builder
     */
    public function __construct() {
        
    }

    /**
     * Creates a SELECT command for a single table.
     * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
     * @param CDbCriteria $criteria the query criteria
     * @param string $alias the alias name of the primary table. Defaults to 't'.
     * @return CDbCommand query command.
     */
    public function createFindCommand(CDbCriteriaBuilder $criteria) {
// 	    $table=$criteria->table;
// 		$alias=$criteria->alias;	
// 		$table=$this->quoteTableName($table);

        $select = is_array($criteria->select) ? implode(', ', $criteria->select) : $criteria->select;

        $tableFrom = $criteria->tableFrom;

        $sql = ($criteria->distinct ? 'SELECT DISTINCT' : 'SELECT') . " {$select} FROM {$tableFrom} ";
        $sql = $this->applyJoin($sql, $criteria->join);
        $sql = $this->applyCondition($sql, $criteria->condition);
        $sql = $this->applyGroup($sql, $criteria->group);
        $sql = $this->applyHaving($sql, $criteria->having);
        $sql = $this->applyOrder($sql, $criteria->order);
        $sql = $this->applyLimit($sql, $criteria->limit, $criteria->offset);

        $sqlPrep = new CDbSqlPrepare();
        $sqlPrep->sqlStr = $sql;
        $sqlPrep->bindVal = $this->bindValues($criteria->params);
        return $sqlPrep;
    }

    /**
     * Creates a COUNT(*) command for a single table.
     * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
     * @param CDbCriteria $criteria the query criteria
     * @param string $alias the alias name of the primary table. Defaults to 't'.
     * @return CDbCommand query command.
     */
    public function createCountCommand(CDbCriteriaBuilder $criteria) {
        $select = is_array($criteria->select) ? implode(', ', $criteria->select) : $criteria->select;

        if (!empty($criteria->group) || !empty($criteria->having)) {
            $sql = ($criteria->distinct ? 'SELECT DISTINCT' : 'SELECT') . " {$select} FROM {$criteria->tableFrom}";
            //$sql=$this->applyJoin($sql,$criteria->join);
            $sql = $this->applyCondition($sql, $criteria->condition);
            $sql = $this->applyGroup($sql, $criteria->group);
            $sql = $this->applyHaving($sql, $criteria->having);
            $sql = "SELECT COUNT(*) FROM ($sql) sq";
        } else {
            if ($criteria->distinct) {
                $sql = "SELECT COUNT(DISTINCT {$criteria->distinct})";
            } else
                $sql = "SELECT COUNT(*) AS `COUNT`";
            $sql.=" FROM $criteria->tableFrom ";
            //$sql=$this->applyJoin($sql,$criteria->join);
            $sql = $this->applyCondition($sql, $criteria->condition);

            $sql = $this->applyGroup($sql, $criteria->group);
            $sql = $this->applyHaving($sql, $criteria->having);
            //$sql=$this->applyOrder($sql,$criteria->order);
            //$sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
        }

        $sqlPrep = new CDbSqlPrepare();
        $sqlPrep->sqlStr = $sql;
        $sqlPrep->bindVal = $this->bindValues($criteria->params);
        return $sqlPrep;

        /* $command=$this->_connection->createCommand($sql);
          $this->bindValues($command,$criteria->params);
          return $command; */
    }

    /**
     * Creates a DELETE command.
     * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
     * @param CDbCriteria $criteria the query criteria
     * @return CDbCommand delete command.
     */
    public function createDeleteCommand(CDbCriteriaBuilder $criteria) {
        $tableFrom = $criteria->tableFrom;

        $sql = "DELETE FROM $tableFrom ";
        $sql = $this->applyCondition($sql, $criteria->condition);
        $sql = $this->applyGroup($sql, $criteria->group);
        $sql = $this->applyHaving($sql, $criteria->having);
        $sql = $this->applyOrder($sql, $criteria->order);
        $sql = $this->applyLimit($sql, $criteria->limit, $criteria->offset);

        $sqlPrep = new CDbSqlPrepare();
        $sqlPrep->sqlStr = $sql;
        $sqlPrep->bindVal = $this->bindValues($criteria->params);
        return $sqlPrep;
    }

    /**
     * Creates an INSERT command.
     * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
     * @param array $data data to be inserted (column name=>column value). If a key is not a valid column name, the corresponding value will be ignored.
     * @return CDbCommand insert command
     */
    public function createInsertCommand(CDbCriteriaBuilder $criteria) {//$table,$data)
        $table = $criteria->table;
        $fields = $criteria->insert;
        $values = $criteria->params;

        $placeholders = array_keys($values);

        $sql = "INSERT INTO {$table} (" . implode(', ', $fields) . ') VALUES (' . implode(', ', $placeholders) . ')';

        $sqlPrep = new CDbSqlPrepare();
        $sqlPrep->sqlStr = $sql;
        $sqlPrep->bindVal = $this->bindValues($values);
        return $sqlPrep;
    }

    /**
     * Creates an UPDATE command.
     * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
     * @param array $data list of columns to be updated (name=>value)
     * @param CDbCriteria $criteria the query criteria
     * @throws CDbException if no columns are being updated for the given table
     * @return CDbCommand update command.
     */
    public function createUpdateCommand(CDbCriteriaBuilder $criteria) {
        $table = $criteria->table;
        $fields = $criteria->update;

        $values = $criteria->params;


        if ($fields === array())
            throw new DbException('No columns are being updated for table "' . $table . '"');

        $sql = "UPDATE `{$table}` SET " . implode(', ', $fields);
        //$sql=$this->applyJoin($sql,$criteria->join);
        $sql = $this->applyCondition($sql, $criteria->condition);
        $sql = $this->applyOrder($sql, $criteria->order);
        $sql = $this->applyLimit($sql, $criteria->limit, $criteria->offset);


        $sqlPrep = new CDbSqlPrepare();
        $sqlPrep->sqlStr = $sql;
        $sqlPrep->bindVal = $this->bindValues($values);
        return $sqlPrep;
    }

    /**
     * Alters the SQL to apply JOIN clause.
     * This method handles the mysql specific syntax where JOIN has to come before SET in UPDATE statement
     * and for DELETE where JOIN has to be after FROM part.
     * @param string $sql the SQL statement to be altered
     * @param string $join the JOIN clause (starting with join type, such as INNER JOIN)
     * @return string the altered SQL statement
     */
    protected function applyJoin($sql, $join) {

        return $sql;
    }

    /**
     * Alters the SQL to apply WHERE clause.
     * @param string $sql the SQL statement without WHERE clause
     * @param string $condition the WHERE clause (without WHERE keyword)
     * @return string the altered SQL statement
     */
    protected function applyCondition($sql, $condition) {
        if ($condition != '')
            return $sql . ' WHERE ' . $condition;
        else
            return $sql;
    }

    /**
     * Alters the SQL to apply ORDER BY.
     * @param string $sql SQL statement without ORDER BY.
     * @param string $orderBy column ordering
     * @return string modified SQL applied with ORDER BY.
     */
    protected function applyOrder($sql, $orderBy) {
        if ($orderBy != '')
            return $sql . ' ORDER BY ' . $orderBy;
        else
            return $sql;
    }

    /**
     * Alters the SQL to apply LIMIT and OFFSET.
     * Default implementation is applicable for PostgreSQL, MySQL and SQLite.
     * @param string $sql SQL query string without LIMIT and OFFSET.
     * @param integer $limit maximum number of rows, -1 to ignore limit.
     * @param integer $offset row offset, -1 to ignore offset.
     * @return string SQL with LIMIT and OFFSET
     */
    protected function applyLimit($sql, $limit, $offset) {
        if ($limit >= 0)
            $sql.=' LIMIT ' . (int) $limit;
        if ($offset > 0)
            $sql.=' OFFSET ' . (int) $offset;
        return $sql;
    }

    /**
     * Alters the SQL to apply GROUP BY.
     * @param string $sql SQL query string without GROUP BY.
     * @param string $group GROUP BY
     * @return string SQL with GROUP BY.
     */
    protected function applyGroup($sql, $group) {
        if ($group != '')
            return $sql . ' GROUP BY ' . $group;
        else
            return $sql;
    }

    /**
     * Alters the SQL to apply HAVING.
     * @param string $sql SQL query string without HAVING
     * @param string $having HAVING
     * @return string SQL with HAVING
     */
    protected function applyHaving($sql, $having) {
        if ($having != '')
            return $sql . ' HAVING ' . $having;
        else
            return $sql;
    }

    /**
     * Binds parameter values for an SQL command.
     * @param CDbCommand $command database command
     * @param array $values values for binding (integer-indexed array for question mark placeholders, string-indexed array for named placeholders)
     */
    protected function bindValues($values) {
        $arBindVal = array();
        if (($n = count($values)) === 0)
            return $arBindVal;
        if (isset($values[0])) { // question mark placeholders
            for ($i = 0; $i < $n;  ++$i) {
                $arBindVal[$i + 1] = $values[$i];
                // echo $i.'-'.$values[$i].'<br>';
            }
// 				$command->bindValue($i+1,$values[$i]);
        } else { // named placeholders
            foreach ($values as $name => $value) {
                if ($name[0] !== ':')
                    $name = ':' . $name;
                //echo $name.'+'.$value.'<br>';
                $arBindVal[$name] = $value;
                //$command->bindValue($name,$value);
            }
        }
        return $arBindVal;
    }

    /**
     * Creates a query criteria.
     * @param mixed $condition query condition or criteria.
     * If a string, it is treated as query condition (the WHERE clause);
     * If an array, it is treated as the initial values for constructing a {@link CDbCriteria} object;
     * Otherwise, it should be an instance of {@link CDbCriteria}.
     * @param array $params parameters to be bound to an SQL statement.
     * This is only used when the first parameter is a string (query condition).
     * In other cases, please use {@link CDbCriteria::params} to set parameters.
     * @return CDbCriteria the created query criteria
     * @throws CException if the condition is not string, array and CDbCriteria
     */
    public function createCriteria($condition = '', $params = array()) {
        if (is_array($condition))
            $criteria = new CDbCriteria($condition);
        elseif ($condition instanceof CDbCriteria) {
            $criteria = clone $condition;
        } else {
            $criteria = new CDbCriteria;
            $criteria->condition = $condition;
            $criteria->params = $params;
        }
        return $criteria;
    }

    /**
     * Quotes a table name for use in a query.
     * If the table name contains schema prefix, the prefix will also be properly quoted.
     * @param string $name table name
     * @return string the properly quoted table name
     * @see quoteSimpleTableName
     */
    protected function quoteTableName($name) {
        if (strpos($name, '.') === false)
            return $this->quoteSimpleTableName($name);
        $parts = explode('.', $name);
        foreach ($parts as $i => $part)
            $parts[$i] = $this->quoteSimpleTableName($part);
        return implode('.', $parts);
    }

    /**
     * Quotes a simple table name for use in a query.
     * A simple table name does not schema prefix.
     * @param string $name table name
     * @return string the properly quoted table name
     * @since 1.1.6
     */
    protected function quoteSimpleTableName($name) {
        return "`" . $name . "`";
    }

    /**
     * Quotes a column name for use in a query.
     * If the column name contains prefix, the prefix will also be properly quoted.
     * @param string $name column name
     * @return string the properly quoted column name
     * @see quoteSimpleColumnName
     */
    protected function quoteColumnName($name) {
        if (($pos = strrpos($name, '.')) !== false) {
            $prefix = $this->quoteTableName(substr($name, 0, $pos)) . '.';
            $name = substr($name, $pos + 1);
        } else
            $prefix = '';
        return $prefix . ($name === '*' ? $name : $this->quoteSimpleColumnName($name));
    }

    /**
     * Quotes a simple column name for use in a query.
     * A simple column name does not contain prefix.
     * @param string $name column name
     * @return string the properly quoted column name
     * @since 1.1.6
     */
    protected function quoteSimpleColumnName($name) {
        return '"' . $name . '"';
    }

}
